﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;


namespace RemoteBank.DAL
{
    class RemoteBankDAL
    {
        SqlConnection conn;
        SqlCommand comman;
        //String conStr = "Server =BANG-PC; Initial Catalog = AssignmentGroup; User Id = sa; Pwd = sa";
        String conStr = "Data Source=DUCKIEN-PC ;Database=AssignmentGroup;User Id=sa;pwd=123456";
        public RemoteBankDAL()
        {
            if(conn == null || conn.State == ConnectionState.Closed)
            {
                this.Connection();
            }
        }
        public void Connection()
        {
            conn = new SqlConnection(conStr);
            conn.Open();
        }

        public void CloseConnection()
        {
            if(conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        public void PrepareCommand(SqlCommand comman,String SPName, List<SqlParameter> ListPara)
        {
            if(conn == null || conn.State == ConnectionState.Closed)
            {
                this.Connection();
            }
            comman.Connection = conn;
            comman.CommandText = SPName;
            comman.CommandTimeout = 30;
            comman.CommandType = CommandType.StoredProcedure;

            if(ListPara != null)
            {
                foreach (SqlParameter para in ListPara)
                {
                    comman.Parameters.Add(para);
                }
            }
            
        }

        public SqlDataReader ExecuteQuerry_GetDataReader(String SPName, List<SqlParameter> ListPara)
        {
            comman = new SqlCommand();
            this.PrepareCommand(comman, SPName, ListPara);
            return comman.ExecuteReader();
        }

        public int ExcuteNonQuery(String SPName, List<SqlParameter> ListPara)
        {
            comman = new SqlCommand();
            this.PrepareCommand(comman,SPName,ListPara);
            int value = comman.ExecuteNonQuery();
            this.CloseConnection();
            return value;
        }
        public SqlDataAdapter ExcuteQuery_GetDataAdapter(String SPName, List<SqlParameter> ListPara)
        {
            comman = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            this.PrepareCommand(comman, SPName, ListPara);
            da.SelectCommand = comman;
            this.CloseConnection();
            return da;
        }

        public void ExcuteScalar(String SPName, List<SqlParameter> ListPara)
        {
            comman = new SqlCommand();
            this.PrepareCommand(comman,SPName,ListPara);
            comman.ExecuteScalar();
            this.CloseConnection();
        }
       
    }
}
